﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Web.SessionState;
namespace Web.User.Exchange
{
    /// <summary>
    /// exchange 的摘要说明
    /// </summary>
    public class exchange : IHttpHandler, IRequiresSessionState
    {


        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/plain";
            common.IsLogin();
            if (context.Request.QueryString["type"] != null)
            {
                string strType = context.Request.QueryString["type"].ToString();
                switch (strType)
                {

                    case "Add_Dep_Storage": Add_Dep_Storage(context); break;
                    case "Update_Dep_Storage": Update_Dep_Storage(context); break;
                    case "GetByID_Dep_Storage": GetByID_Dep_Storage(context); break;

                    case "GetStorageInfoByID": GetStorageInfoByID(context); break;
                    case "GetInterestState": GetInterestState(context); break;


                    case "GetDepInfoByStorageID": GetDepInfoByStorageID(context); break;

                    case "GetStorageRateByID": GetStorageRateByID(context); break;
                        
                }
            }

        }

       

        /// <summary>
        /// 添加储户存储信息
        /// </summary>
        /// <param name="context"></param>
        void Add_Dep_Storage(HttpContext context)
        {

            string AccountNumber = context.Request.QueryString["AccountNumber"].ToString();
            string StorageRateID = context.Request.Form["StorageRateID"].ToString();
            string VarietyID = context.Request.Form["VarietyID"].ToString();
            string TypeID = context.Request.Form["TypeID"].ToString();

            string TimeID = context.Request.Form["TimeID"].ToString();
            string StorageFee = context.Request.Form["StorageFee"].ToString();
            string WeighNo = context.Request.Form["WeighNO"].ToString();
            string StorageNumber = context.Request.Form["StorageNumber"].ToString();

            string CurrentRate = "";
            string Price_ShiChang = "";
            string Price_DaoQi = "";
            string Price_HeTong = "";

            //由价格与利率表获取当前的价格信息
            StringBuilder strSqlPrice = new StringBuilder();
            strSqlPrice.Append(" select ID,TypeID,VarietyID,VarietyLevelID,TimeID,StorageFee,BankRate,");
            strSqlPrice.Append(" CurrentRate,EarningRate,Price_ShiChang,Price_DaoQi,Price_HeTong,Price_XiaoShou ");
            strSqlPrice.Append(" FROM  dbo.StorageRate");
            strSqlPrice.Append("  WHERE VarietyID= " + VarietyID + " and TypeID=" + TypeID + " and TimeID=" + TimeID);
            DataTable dtPrice = SQLHelper.ExecuteDataTable(strSqlPrice.ToString());
            if (dtPrice != null && dtPrice.Rows.Count != 0)
            {
                CurrentRate = dtPrice.Rows[0]["CurrentRate"].ToString();
                Price_ShiChang = dtPrice.Rows[0]["Price_ShiChang"].ToString();
                Price_DaoQi = dtPrice.Rows[0]["Price_DaoQi"].ToString();
                Price_HeTong = dtPrice.Rows[0]["Price_HeTong"].ToString();
            }
            else
            {
                context.Response.Write("Price");
                return;
            }



            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [Dep_StorageInfo] (");
            strSql.Append("AccountNumber,StorageRateID,VarietyID,TypeID,TimeID,StorageDate,WeighNo,StorageNumber,StorageFee,CurrentRate,Price_ShiChang,Price_DaoQi,Price_HeTong)");
            strSql.Append(" values (");
            strSql.Append("@AccountNumber,@StorageRateID,@VarietyID,@TypeID,@TimeID,@StorageDate,@WeighNo,@StorageNumber,@StorageFee,@CurrentRate,@Price_ShiChang,@Price_DaoQi,@Price_HeTong)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@AccountNumber", SqlDbType.NVarChar,50),
					new SqlParameter("@StorageRateID", SqlDbType.Int,4),
					new SqlParameter("@VarietyID", SqlDbType.Int,4),
					new SqlParameter("@TypeID", SqlDbType.Int,4),
					new SqlParameter("@TimeID", SqlDbType.Int,4),
					new SqlParameter("@StorageDate", SqlDbType.DateTime),
					new SqlParameter("@WeighNo", SqlDbType.NVarChar,50),
					new SqlParameter("@StorageNumber", SqlDbType.Int,4),
					new SqlParameter("@StorageFee", SqlDbType.Decimal,9),
					new SqlParameter("@CurrentRate", SqlDbType.Decimal,9),
					new SqlParameter("@Price_ShiChang", SqlDbType.Decimal,9),
					new SqlParameter("@Price_DaoQi", SqlDbType.Decimal,9),
					new SqlParameter("@Price_HeTong", SqlDbType.Decimal,9)};
            parameters[0].Value = AccountNumber;
            parameters[1].Value = StorageRateID;
            parameters[2].Value = VarietyID;
            parameters[3].Value = TypeID;
            parameters[4].Value = TimeID;
            parameters[5].Value = DateTime.Now;
            parameters[6].Value = WeighNo;
            parameters[7].Value = StorageNumber;
            parameters[8].Value = StorageFee;
            parameters[9].Value = CurrentRate;
            parameters[10].Value = Price_ShiChang;
            parameters[11].Value = Price_DaoQi;
            parameters[12].Value = Price_HeTong;
            if (SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }
        //暂未修改
        void Update_Dep_Storage(HttpContext context)
        {
            string ID = context.Request.QueryString["ID"].ToString();

            string WeighNo = context.Request.Form["WeighNO"].ToString();
            string StorageNumber = context.Request.Form["StorageNumber"].ToString();

            string strSql = "UPDATE dbo.Dep_StorageInfo SET WeighNo='" + WeighNo + "',StorageNumber=" + StorageNumber + " WHERE ID=" + ID;

            if (SQLHelper.ExecuteNonQuery(strSql.ToString()) != 0)
            {
                context.Response.Write("OK");
            }
            else
            {
                context.Response.Write("Error");
            }
        }

        /// <summary>
        /// 添加储户存储信息
        /// </summary>
        /// <param name="context"></param>
        void GetByID_Dep_Storage(HttpContext context)
        {
            string ID = context.Request.QueryString["ID"].ToString();

            //获取存粮信息
            StringBuilder strSqlStorage = new StringBuilder();
            strSqlStorage.Append("SELECT A.ID,A.StorageNumber,A.StorageDate,A.WeighNo, A.AccountNumber,B.strName AS VarietyID,A.Price_ShiChang,A.Price_DaoQi,C.strName AS TimeID,A.StorageFee");
            strSqlStorage.Append("  FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageVariety B ON A.VarietyID=B.ID");
            strSqlStorage.Append("  INNER JOIN dbo.StorageTime C ON A.TimeID=C.ID");
            strSqlStorage.Append("  WHERE A.ID=" + ID);
            DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());

            if (dtStorage != null && dtStorage.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dtStorage));
            }
            else
            {
                context.Response.Write("Error");
            }
        }



        /// <summary>
        /// 根据Dep_Storage表的ID查询单条存储信息
        /// </summary>
        /// <param name="context"></param>
        void GetStorageInfoByID(HttpContext context)
        {
            string ID = context.Request.QueryString["ID"].ToString();

            //获取存粮信息
            StringBuilder strSqlStorage = new StringBuilder();
            strSqlStorage.Append("SELECT A.VarietyID as txtVarietyID,A.TypeID as txtTypeID, A.TimeID as txtTimeID, B.strName AS VarietyID,D.strName as UnitID, A.StorageNumber,A.StorageNumberRaw,convert(varchar(10),StorageDate,120) AS StorageDate,");
            strSqlStorage.Append("  A.Price_ShiChang,C.strName AS TimeID,DATEDIFF(DAY,A.StorageDate,GETDATE())AS numDate");
            strSqlStorage.Append("  FROM  dbo.Dep_StorageInfo A INNER JOIN dbo.StorageVariety B ON A.VarietyID=B.ID");
            strSqlStorage.Append("  INNER JOIN dbo.StorageTime C ON A.TimeID=C.ID");
            strSqlStorage.Append("  INNER JOIN dbo.BD_MeasuringUnit D ON B.MeasuringUnitID=D.ID");
            strSqlStorage.Append("  WHERE A.ID=" + ID);
            DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());

            if (dtStorage != null && dtStorage.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dtStorage));
            }
            else
            {
                context.Response.Write("Error");
            }
        }


        /// <summary>
        ///  获取当前存储的可结息状态
        /// </summary>
        /// <param name="context"></param>
        void GetInterestState(HttpContext context)
        {

             
            string ID = context.Request.QueryString["ID"].ToString();
            int ISJiexi = 0;//是否允许仅结息操作
            double Interest = 0;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select B.InterestType,InterestDate,StorageDate,B.numStorageDate,WeighNo,StorageNumber,StorageFee,CurrentRate,Price_ShiChang,Price_DaoQi,Price_HeTong ");
            strSql.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageTime B ON A.TimeID=B.ID");
            strSql.Append(" WHERE A.ID=" + ID);
            DataTable dt = SQLHelper.ExecuteDataTable(strSql.ToString());
            if (dt != null && dt.Rows.Count != 0)
            {
                int InterestType = Convert.ToInt32(dt.Rows[0]["InterestType"]);//利息计算方式
                int StorageNumber = Convert.ToInt32(dt.Rows[0]["StorageNumber"]);//存储数量
                DateTime StorageDate = Convert.ToDateTime(dt.Rows[0]["StorageDate"]);//存入日期
                DateTime InterestDate = Convert.ToDateTime(dt.Rows[0]["InterestDate"]);
                TimeSpan tsStorage = DateTime.Now.Subtract(StorageDate);
                TimeSpan tsInterest = DateTime.Now.Subtract(InterestDate);
                int numStorageDate = Convert.ToInt32(dt.Rows[0]["numStorageDate"]);//约定存储时间
                double CurrentRate = Convert.ToDouble(dt.Rows[0]["CurrentRate"]);//活期利率
                double Price_ShiChang = Convert.ToDouble(dt.Rows[0]["Price_ShiChang"]);//市场价
                double Price_DaoQi = Convert.ToDouble(dt.Rows[0]["Price_DaoQi"]);//到期价
                switch (InterestType)
                {
                    case 1://按月结息方式
                        Interest = StorageNumber * CurrentRate * tsInterest.TotalDays / (double)30;
                        ISJiexi = 1;
                        break;
                    case 2://按市场价结息方式
                        if (tsStorage.TotalDays < numStorageDate)
                        {
                            Interest = -1;
                        }
                        else
                        {
                            //计算当前的市场价格
                            StringBuilder strSqlShiChang = new StringBuilder();
                            strSqlShiChang.Append(" SELECT B.Price_ShiChang,B.EarningRate");
                            strSqlShiChang.Append(" FROM dbo.Dep_StorageInfo A INNER JOIN dbo.StorageRate B ON A.StorageRateID=B.ID");
                            strSqlShiChang.Append(" WHERE A.ID=" + ID);
                            DataTable dtShiChang = SQLHelper.ExecuteDataTable(strSqlShiChang.ToString());
                            if (dtShiChang != null && dtShiChang.Rows.Count != 0)
                            {
                                double Price_JieCun = Convert.ToDouble(dtShiChang.Rows[0]["Price_ShiChang"]);
                                int EarningRate = Convert.ToInt32(dtShiChang.Rows[0]["EarningRate"]);
                                if (Price_JieCun >= Price_ShiChang)//到期的结存价格比现在的市场价高
                                {
                                    Interest = (Price_JieCun - Price_ShiChang) * StorageNumber * EarningRate / (double)100;
                                  
                                }
                                else
                                {
                                    Interest = -(Price_ShiChang - Price_JieCun) * StorageNumber;
                                  }
                            }
                        }
                        break;
                    case 3://定期结息方式
                        if (tsStorage.TotalDays < numStorageDate)
                        {
                            Interest = -1;
                        }
                        else
                        {
                            Interest = (Price_DaoQi - Price_ShiChang) * StorageNumber;
                           
                        }
                        break;
                }
               
            }
            Interest = Math.Round(Interest, 2);
            string strReturn="[{\"Interest\":\""+Interest+"\",\"ISJiexi\":\""+ISJiexi+"\"}]";
            context.Response.Write(strReturn);
        }


        /// <summary>
        /// 根据存期类型和存储产品类型来确定一条价格比例
        /// </summary>
        /// <param name="context"></param>
        void GetStorageRateByID(HttpContext context)
        {
            string TimeID = context.Request.QueryString["TimeID"].ToString();
            string VarietyID = context.Request.QueryString["VarietyID"].ToString();

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select A.ID,A.TypeID,A.VarietyID,A.VarietyLevelID,A.TimeID,A.StorageFee,A.BankRate,A.CurrentRate,A.EarningRate,");
            strSql.Append("A.Price_ShiChang,A.Price_DaoQi,A.Price_HeTong,A.Price_XiaoShou,B.ISRegular,B.InterestType,B.numStorageDate,B.PricePolicy");
            strSql.Append(" FROM dbo.StorageRate A INNER JOIN dbo.StorageTime B ON A.TimeID=B.ID ");
        
            strSql.Append(" where A.TimeID=" + TimeID + " and  A.VarietyID=" + VarietyID);

            //获取存粮信息

            DataTable dtStorage = SQLHelper.ExecuteDataTable(strSql.ToString());

            if (dtStorage != null && dtStorage.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dtStorage));
            }
            else
            {
                context.Response.Write("Error");
            }
        }
        /// <summary>
        /// 根据当前存储信息查找存储产品的储户信息
        /// </summary>
        /// <param name="context"></param>
        void GetDepInfoByStorageID(HttpContext context)
        {
            string ID = context.Request.QueryString["ID"].ToString();

            //获取存粮信息
            StringBuilder strSqlStorage = new StringBuilder();
            strSqlStorage.Append("select  A.AccountNumber,A.strAddress,A.strName,A.PhoneNO,A.ISSendMessage,A.BankCardNO,");
            strSqlStorage.Append("  CASE( A.IDCard) WHEN '' THEN '未填写' ELSE '******' END AS IDCard,");
            strSqlStorage.Append(" CASE (A.numState) WHEN '1' THEN '正常' ELSE '挂失' END AS numState");
            strSqlStorage.Append("  FROM dbo.Depositor A INNER JOIN dbo.Dep_StorageInfo B ON B.AccountNumber=A.AccountNumber");
            strSqlStorage.Append("  WHERE B.ID=" + ID);
            DataTable dtStorage = SQLHelper.ExecuteDataTable(strSqlStorage.ToString());

            if (dtStorage != null && dtStorage.Rows.Count != 0)
            {
                context.Response.Write(JsonHelper.ToJson(dtStorage));
            }
            else
            {
                context.Response.Write("Error");
            }
        }


        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}